﻿<%@ WebHandler Language="C#" Class="SIPO.Web.AjaxHandler.Transaction.recSanctionhandler" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

using SIPO.Library.DataAccess.Transaction;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.DataAccess.Administration;
using SIPO.Library.DomainObject.Administration;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DataAccess.Administration.Catalog;

using SIPO.Library.Enum;
using SIPO.Web.Support;

using Newtonsoft.Json.Linq;

using System.Web.SessionState;

namespace SIPO.Web.AjaxHandler.Transaction
{

    public class recSanctionhandler : SIPO.Web.BaseHandler, IRequiresSessionState
    {

        private recSanctionRepo recSanctionDal = new recSanctionRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recLogRepo logDal = new recLogRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private string MethodName = string.Empty;
        
        public override void ProcessRequest(HttpContext context)
        {
            base.ProcessRequest(context);

            MethodName = context.Request.Params["method"];

            switch (MethodName.ToLower())
            {
                case ("insert"):
                    context.Response.Write(Insert(context));
                    break;
                case ("update"):
                    context.Response.Write(Update(context));
                    break;
                case ("delete"):
                    System.IO.StreamReader sr = new System.IO.StreamReader(context.Request.InputStream);
                    string jsonString = string.Empty;
                    jsonString = context.Server.UrlDecode(sr.ReadToEnd());
                    JObject jObj = string.IsNullOrEmpty(jsonString) ? null : JObject.Parse(jsonString);
                    sr.Close();
                    sr.Dispose();
                    context.Response.Write(Delete(context, jObj));
                    break;
                case ("review"):
                    sr = new System.IO.StreamReader(context.Request.InputStream);
                    jsonString = string.Empty;
                    jsonString = context.Server.UrlDecode(sr.ReadToEnd());
                    jObj = string.IsNullOrEmpty(jsonString) ? null : JObject.Parse(jsonString);
                    sr.Close();
                    sr.Dispose();
                    context.Response.Write(UpdateReview(context, jObj));
                    break;
                case ("approve"):
                    sr = new System.IO.StreamReader(context.Request.InputStream);
                    jsonString = string.Empty;
                    jsonString = context.Server.UrlDecode(sr.ReadToEnd());
                    jObj = string.IsNullOrEmpty(jsonString) ? null : JObject.Parse(jsonString);
                    sr.Close();
                    sr.Dispose();
                    context.Response.Write(UpdateApprove(context, jObj));
                    break;
                case ("open"):
                    sr = new System.IO.StreamReader(context.Request.InputStream);
                    jsonString = string.Empty;
                    jsonString = context.Server.UrlDecode(sr.ReadToEnd());
                    jObj = string.IsNullOrEmpty(jsonString) ? null : JObject.Parse(jsonString);
                    sr.Close();
                    sr.Dispose();
                    context.Response.Write(Open(context, jObj));
                    break;
                case ("getdata"):
                    context.Response.Write(GetData(context));
                    break;
                case ("getdatareport"):
                    context.Response.Write(GetDataReport(context));
                    break;
                default:
                    // do nothing
                    break;
            }

        }

        public override bool IsReusable
        {
            get
            {
                return false;
            }
        }


        private string Insert(HttpContext context)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                var data = context.Request.Form;

                var ddlDebtorSector = context.Server.UrlDecode(data["ddlDebtorSector"]);
                var ddlSanctionType = context.Server.UrlDecode(data["ddlSanctionType"]);

                var txtLetterNo = context.Server.UrlDecode(data["txtLetterNo"]);
                var decodeLetterDate = context.Server.UrlDecode(data["txtLetterDate"].ToString());
                var txtLetterDate = string.IsNullOrEmpty(context.Server.UrlDecode(data["txtLetterDate"])) ? (DateTime?)null : Helper.FormatDate(decodeLetterDate, Helper.Culture.Indonesian);

                var hfID = context.Server.UrlDecode(data["hfID"]);
                var txtValue = context.Server.UrlDecode(data["txtValue"]);
                                
                recSanction obj = new recSanction();
                obj.DebtorSectorID = int.Parse(ddlDebtorSector);
                obj.SanctionTypeID = int.Parse(ddlSanctionType);
                obj.Value = Helper.ToDecimal(txtValue, System.Globalization.NumberStyles.Number, Helper.Culture.Indonesian); // because from UI form is id-ID formatted.
                obj.LetterNo = txtLetterNo;
                obj.LetterDate = txtLetterDate;

                if (context.Request.Files.Count > 0)
                {
                    HttpPostedFile hpf = context.Request.Files[0] as HttpPostedFile;
                    string FileName = string.Empty;
                    if (HttpContext.Current.Request.Browser.Browser.ToUpper() == "IE")
                    {
                        string[] files = hpf.FileName.Split(new char[] { '\\' });
                        FileName = files[files.Length - 1];
                    }
                    else
                    {
                        FileName = hpf.FileName;
                    }

                    byte[] datas = new byte[hpf.ContentLength];
                    hpf.InputStream.Position = 0;
                    hpf.InputStream.Read(datas, 0, hpf.ContentLength);

                    obj.Attachment = datas;
                    obj.AttachedFileName = FileName;

                    hpf.InputStream.Close();
                    hpf.InputStream.Dispose();
                }

                obj.IsReview = false;
                obj.IsApprove = false;
                obj.CreateDate = DateTime.Now;
                obj.CreateBy = base.UserLoggedIn;

                recSanctionDal.Save(obj);

                helper.Message = "Data berhasil di simpan !";
                helper.IsSuccess = true;
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Insert";
                log.LogMessage = helper.Message;
                log.LogSource = "recSanctionhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);

            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string Update(HttpContext context)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                var data = context.Request.Form;

                var ddlDebtorSector = context.Server.UrlDecode(data["ddlDebtorSector"]);
                var ddlSanctionType = context.Server.UrlDecode(data["ddlSanctionType"]);

                var txtLetterNo = context.Server.UrlDecode(data["txtLetterNo"]);
                var txtLetterDate = string.IsNullOrEmpty(context.Server.UrlDecode(data["txtLetterDate"])) ? (DateTime?)null : Helper.FormatDate(context.Server.UrlDecode(data["txtLetterDate"]), Helper.Culture.Indonesian);

                var hfID = context.Server.UrlDecode(data["hfID"]);
                var txtValue = context.Server.UrlDecode(data["txtValue"]);
                
                recSanction obj = recSanctionDal.GetById(int.Parse(hfID));
                if (obj != null)
                {
                    if (obj.IsReview == false || obj.IsReview == null)
                    {
                        obj = new recSanction();
                        obj.SanctionID = int.Parse(hfID);
                        obj.DebtorSectorID = int.Parse(ddlDebtorSector);
                        obj.SanctionTypeID = int.Parse(ddlSanctionType);
                        obj.Value = Helper.ToDecimal(txtValue, System.Globalization.NumberStyles.Number, Helper.Culture.Indonesian); // because from UI form is id-ID formatted.
                        obj.LetterNo = txtLetterNo;
                        obj.LetterDate = txtLetterDate;

                        if (context.Request.Files.Count > 0)
                        {
                            HttpPostedFile hpf = context.Request.Files[0] as HttpPostedFile;
                            string fileName = string.Empty;
                            if (HttpContext.Current.Request.Browser.Browser.ToUpper() == "IE")
                            {
                                string[] files = hpf.FileName.Split(new char[] { '\\' });
                                fileName = files[files.Length - 1];
                            }
                            else
                            {
                                fileName = hpf.FileName;
                            }

                            byte[] datas = new byte[hpf.ContentLength];
                            hpf.InputStream.Position = 0;
                            hpf.InputStream.Read(datas, 0, hpf.ContentLength);

                            obj.Attachment = datas;
                            obj.AttachedFileName = fileName;

                            hpf.InputStream.Close();
                            hpf.InputStream.Dispose();
                        }

                        obj.UpdatedDate = DateTime.Now;
                        obj.UpdatedBy = base.UserLoggedIn;

                        if (context.Request.Files.Count > 0)
                            recSanctionDal.Update(obj);
                        else
                            recSanctionDal.UpdateWithoutFile(obj);

                        helper.Message = "Data berhasil di ubah !";
                        helper.IsSuccess = true;
                    }
                    else
                    {
                        helper.Message = "Data tidak dapat di ubah karena data sudah di review!";
                        helper.IsSuccess = true;
                    }
                }

            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Update";
                log.LogMessage = helper.Message;
                log.LogSource = "recSanctionhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string UpdateReview(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                    recSanction getObj = recSanctionDal.GetById((int)jo["SanctionID"]);
                    recSanction obj = new recSanction();
                    obj.SanctionID = (int)jo["SanctionID"];
                    obj.IsReview = true;
                    obj.ReviewBy = base.UserLoggedIn;
                    obj.ReviewDate = DateTime.Now;
                    obj.UpdatedDate = DateTime.Now;
                    obj.UpdatedBy = base.UserLoggedIn;

                    recSanctionDal.UpdateReview(obj);

                    helper.Message = "Review data berhasil";
                    helper.IsSuccess = true;

            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Update Review";
                log.LogMessage = helper.Message;
                log.LogSource = "recSanctionhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string UpdateApprove(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                recSanction getObj = recSanctionDal.GetById((int)jo["SanctionID"]);
                if (getObj != null)
                {
                        // objek sanction
                        recSanction obj = new recSanction();
                        obj.SanctionID = (int)jo["SanctionID"];
                        obj.IsApprove = true;
                        obj.ApproveDate = DateTime.Now;
                        obj.ApproveBy = base.UserLoggedIn;
                        obj.UpdatedDate = DateTime.Now;
                        obj.UpdatedBy = base.UserLoggedIn;
                        // end
                    
                        //object recBilling
                        recBillingRepo recBillingRepo = new recBillingRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                        recTransactionTypeRepo recTransactionDal = new recTransactionTypeRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                                
                        recBilling objBilling = new recBilling();
                        //objBilling.BillingNo = "3." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recBillingRepo.GetMaxID("3").ToString().PadLeft(5, '0');//Guid.NewGuid().ToString().Substring(0, 9);
                        objBilling.BillingNo = recBillingRepo.GenerateBillingNoSP("3");
                        objBilling.DebtorSectorID = getObj.DebtorSectorID;

                        recDayOffRepo recDayOffDal = new recDayOffRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                                
                        //get Tipe Transaksi  
                        List<recTransactionType> listTransType = recTransactionDal.GetList();
                        var query = listTransType.Where(x=>x.TransTypeName.ToLower().Contains("sanksi"));
                        listTransType = query.ToList();
                        listTransType.ForEach(delegate(recTransactionType data)
                        {
                            objBilling.TransTypeID = data.TransTypeID;
                            if(data.TermDay.HasValue)
                                objBilling.DueDate = recDayOffDal.GetDueDate(getObj.LetterDate.Value.AddDays(Convert.ToDouble(data.TermDay)).ToString("yyyyMMdd"));
                        });
                        //end


                        objBilling.ForPayment = getObj.LetterNo;
                        objBilling.Value = getObj.Value;
                        objBilling.CreatedDate = DateTime.Now;
                        objBilling.CreatedBy = base.UserLoggedIn;
                        objBilling.Balance = getObj.Value;
                        objBilling.BillingDate = DateTime.Now;
                        // end


                        recSanctionDal.UpdateApprove(obj, objBilling);

                        helper.Message = "Persetujuan data berhasil !";
                        helper.IsSuccess = true;

                               
                        
                }

            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Update Approve";
                log.LogMessage = helper.Message;
                log.LogSource = "recSanctionhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string Delete(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                var strId = (string)jo["SanctionID"];
                bool isReview = false;
                string[] ids = strId.Split(',');
                foreach (string id in ids)
                {
                    int intID = Convert.ToInt32(id);

                    recSanction obj = recSanctionDal.GetById(intID);
                    if (obj != null)
                    {
                        if (obj.IsReview == true)
                        {
                            isReview = true;
                            break;
                        }
                    }
                    recSanctionDal.Delete(intID);
                }

                if (isReview)
                {
                    helper.IsSuccess = true;
                    helper.Message = "Data tidak dapat di hapus karena sudah ter review";
                }
                else
                {
                    helper.IsSuccess = true;
                    helper.Message = "Data berhasil di hapus !";
                }
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Delete";
                log.LogMessage = helper.Message;
                log.LogSource = "recSanctionhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);

            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string Open(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();
            try
            {
                recSanction getObj = recSanctionDal.GetById((int)jo["SanctionID"]);
                
                recSanction obj = new recSanction();
                    obj = recSanctionDal.GetById(Convert.ToInt32(jo["SanctionID"]));
                    if (obj != null)
                    {
                        if (obj.BillingNo.Trim().Length > 0)
                        {
                            recBillingRepo recBillingDal = new recBillingRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);

                            if (recBillingDal.Delete(obj.BillingNo.Trim()))
                            {
                                obj = new recSanction();
                                obj.SanctionID = (int)jo["SanctionID"];
                                obj.UpdatedDate = DateTime.Now;
                                obj.UpdatedBy = base.UserLoggedIn;
                                recSanctionDal.Open(obj);
                                helper.Message = "Open data berhasil !";
                                helper.IsSuccess = true;
                            }
                        }
                        else
                        {
                            obj = new recSanction();
                            obj.SanctionID = (int)jo["SanctionID"];
                            obj.UpdatedDate = DateTime.Now;
                            obj.UpdatedBy = base.UserLoggedIn;
                            recSanctionDal.Open(obj);
                            helper.Message = "Open data berhasil !";
                            helper.IsSuccess = true;
                        }
                        
                    }
                   
                }


           // }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Update Approve";
                log.LogMessage = helper.Message;
                log.LogSource = "recSanctionhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string GetData(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);

            if (base.DebtorID != null && base.DebtorID != 0)
            {
                parameters = parameters == string.Empty ? " WHERE recDebtor.DebtorID=" + base.DebtorID +" AND recSanction.IsReview = 1 AND recSanction.IsApprove=1": parameters + " AND recDebtor.DebtorID=" + base.DebtorID +" AND recSanction.IsReview = 1 AND recSanction.IsApprove=1";
            }

            if (base.SubSubSectorIDs != null)
                parameters = parameters == string.Empty ? " WHERE recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + ")" : parameters + " AND recDebtorSector.SubSubSectorID IN (" + base.SubSubSectorIDs + ")";

            
            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recSanction.Fields.DebtorSectorID : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recSanction> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recSanctionDal.SelectScalar(SQL.Function.Aggregate.Count, recSanction.Fields.SanctionID, parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recSanctionDal.GetList(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetData";
                log.LogMessage = ex.Message;
                log.LogSource = "recSanctionhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);

        }

        public string GetDataReport(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);

            recSanction objSanction1 = new recSanction(true);
            recSanction objSanction2 = new recSanction();

            short isReview = 3;
            short isApprove = 3;

            objSanction1.LetterDate = context.Request["PeriodeAwal"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAwal"], Helper.Culture.Indonesian);
            objSanction2.LetterDate = context.Request["PeriodeAkhir"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAkhir"], Helper.Culture.Indonesian);
            objSanction1.recDebtorSector.DebtorID = context.Request["DebtorID"].Equals(string.Empty) ? 0 : Convert.ToInt32(context.Request["DebtorID"].ToString());
            objSanction1.SanctionTypeID = context.Request["SanctionTypeID"].Equals(string.Empty) ? 0 : Convert.ToInt32(context.Request["SanctionTypeID"].ToString());
            isReview = context.Request["IsReview"].Equals(string.Empty) ? (short)0 : short.Parse(context.Request["IsReview"].ToString());
            isApprove = context.Request["IsApprove"].Equals(string.Empty) ? (short)0 : short.Parse(context.Request["IsApprove"].ToString());

            //konversi tanggal
            string periodeAwal = string.Format("{0:yyyyMMdd}", objSanction1.LetterDate);
            string periodeAkhir = string.Format("{0:yyyyMMdd}", objSanction2.LetterDate);

            //filter isReview
            if (isReview == 1)
                parameters += "WHERE recSanction.IsReview = 'false'";
            else if (isReview == 2)
                parameters += "WHERE recSanction.IsReview = 'true'";

            //filter isApprove
            if (isReview == 3)
            {
                if (isApprove == 1)
                    parameters += "WHERE recSanction.IsApprove = 'false'";
                else if (isApprove == 2)
                    parameters += "WHERE recSanction.IsApprove = 'true'";
            }
            else
            {
                if (isApprove == 1)
                    parameters += " AND recSanction.IsApprove = 'false'";
                else if (isApprove == 2)
                    parameters += " AND recSanction.IsApprove = 'true'";
            }

            //filter letterDate
            if (isReview == 3 && isApprove == 3)
            {
                if (!periodeAwal.Equals(string.Empty) && !periodeAkhir.Equals(string.Empty))
                    parameters += "WHERE recSanction.LetterDate BETWEEN ISNULL ('" + periodeAwal + "', recSanction.LetterDate) AND ISNULL ('" + periodeAkhir + "', recSanction.LetterDate)";
            }
            else
            {
                if (!periodeAwal.Equals(string.Empty) && !periodeAkhir.Equals(string.Empty))
                    parameters += " AND recSanction.LetterDate BETWEEN ISNULL ('" + periodeAwal + "', recSanction.LetterDate) AND ISNULL ('" + periodeAkhir + "', recSanction.LetterDate)";
            }
            
            //filter debtorID
            if (isReview == 3 && isApprove == 3 && periodeAwal.Equals(string.Empty) && periodeAkhir.Equals(string.Empty))
            {
                if (objSanction1.recDebtorSector.DebtorID != 0)
                    parameters += "WHERE recDebtorSector.DebtorID = ISNULL (" + objSanction1.recDebtorSector.DebtorID + ", recDebtorSector.DebtorID)";
            }
            else if (objSanction1.recDebtorSector.DebtorID != 0)
            {
                    parameters += " AND recDebtorSector.DebtorID = ISNULL (" + objSanction1.recDebtorSector.DebtorID + ", recDebtorSector.DebtorID)";
            }

            //filter sanctionTypeID
            if (isReview == 3 && isApprove == 3 && periodeAwal.Equals(string.Empty) && periodeAkhir.Equals(string.Empty) && objSanction1.recDebtorSector.DebtorID == 0)
            {
                if (objSanction1.SanctionTypeID != 0)
                    parameters += "WHERE recSanction.SanctionTypeID = ISNULL (" + objSanction1.SanctionTypeID + ", recSanction.SanctionTypeID)";
            }
            else if (objSanction1.SanctionTypeID != 0)
            {
                    parameters += " AND recSanction.SanctionTypeID = ISNULL (" + objSanction1.SanctionTypeID + ", recSanction.SanctionTypeID)";
            }
            
            
            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recSanction.Fields.DebtorSectorID : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recSanction> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recSanctionDal.SelectScalar(SQL.Function.Aggregate.Count, recSanction.Fields.SanctionID, parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recSanctionDal.GetList(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataReport";
                log.LogMessage = ex.Message;
                log.LogSource = "recSanctionhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);
        }

    }

}